package deweb.cimaplanning.beans;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;

import com.cs.common.pool.DBDAO;

import deweb.cimaplanning.misc;
import deweb.cimaplanning.dto.PORegionCosts;

public class PORegionCostsBean {
	protected int versionId;
	
	protected boolean successful;
	protected String errorMsg;
	
	protected HttpServletRequest request;
	
	public PORegionCostsBean(int versionId, HttpServletRequest request) {
		this.versionId	= versionId;
		this.request	= request;
	}
	
	public ArrayList browse(){
		DBDAO dbConn	= null;
		Connection conn = null;
		Statement stmt	= null;
		ResultSet rs	= null;
		String sql		= null;
		
		try {
		    dbConn	= new DBDAO();
		    conn 	= dbConn.getConnection(misc.DS_NAME);
		    stmt 	= conn.createStatement();
			
			sql = "";

			sql  = "SELECT pov.purchase_order_version_id, pov.biweek_period_version_id,  ";
			sql += "		vpv.month_period_version_id,  ";
			sql += "		SUM(cr.cost) amount, cr.tons ";
			sql += "	FROM costs_po_in_regions cr ";
			sql += "		INNER JOIN regions_batches rb ";
			sql += "			ON cr.region_batch_id = rb.region_batch_id ";
			sql += "		INNER JOIN vw_v_purchases_orders_versions pov ";
			sql += "			ON cr.purchase_order_version_id = pov.purchase_order_version_id ";
			sql += "		INNER JOIN vw_periods_versions vpv ";
			sql += "			ON pov.biweek_period_version_id = vpv.biweek_period_version_id ";
			sql += "			AND pov.version_id = vpv.version_id ";
			sql += "		LEFT OUTER JOIN transports t ";
			sql += "			ON pov.transport_id = t.transport_id ";
			sql += "	WHERE rb.version_id = 41" + versionId + " ";
			sql += "	GROUP BY pov.purchase_order_version_id, pov.biweek_period_version_id,  ";
			sql += "		vpv.month_period_version_id, cr.tons,  ";
			sql += "		pov.is_transit, pov.transit_date, t.priority ";
			sql += "	ORDER BY pov.biweek_period_version_id, vpv.month_period_version_id,  ";
			sql += "		pov.is_transit DESC, pov.transit_date, t.priority DESC ";
			
			System.out.println("sql: [" + sql + "]");
			rs = stmt.executeQuery(sql);
			
			ArrayList rpo = new ArrayList(100);
			PORegionCosts rpoc = null;
			
			while (rs.next()){
				rpoc = new PORegionCosts();
				
				rpoc.setAmount(rs.getDouble("amount"));
				rpoc.setBiweekPeriodVersionId(rs.getInt("biweek_period_version_id"));
				rpoc.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
				rpoc.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
				rpoc.setTons(rs.getDouble("tons"));
								
				rpo.add(rpoc);
			}
			
			rpo.trimToSize();
			
			return rpo;
			
		} catch (Exception e){
			successful	= false;
			errorMsg	= e.getMessage();
			
			return null;
		} finally {
		    try { conn.close(); } catch (Exception e) {}
		    try { stmt.close(); } catch (Exception e) {}
		    try { rs.close();   } catch (Exception e) {}
		}
	}
	
	/**
	 * @return the successful
	 */
	public boolean isSuccessful() {
		return successful;
	}

	/**
	 * @return the errorMsg
	 */
	public String getErrorMsg() {
		return errorMsg;
	}
}
